package day1201parking;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import day1130.JDBCUtil;

public class CarSpotDao {
	//插入数据
	public static void insert(CarSpot cs) {
		String sql = "insert into carspot(id,status,number) values(?,?,?)";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, cs.getId());
			ps.setString(2, cs.getStatus());
			ps.setString(3, cs.getNumber());
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.close(conn, ps);
		}
	}
	
	//查询车位信息
	public static ArrayList<CarSpot> select(CarSpot cs){
		ArrayList<CarSpot> list = new ArrayList<CarSpot>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet result = null;
		List<Object> vals = new ArrayList<>();
		StringBuffer sql = new StringBuffer("select * from carspot where 1=1");
		String id = cs.getId();
		if(id!=null&&!id.trim().isEmpty()) {
			sql.append(" and id = ?");
			vals.add(id);
		}
		String status = cs.getStatus();
		if(status!=null&&!status.trim().isEmpty()) {
			sql.append(" and status = ?");
			vals.add(status);
		}
		String number = cs.getNumber();
		if(number!=null&&!number.trim().isEmpty()) {
			sql.append(" and number = ?");
			vals.add(number);
		}
		
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			for(int i=0;i<vals.size(); i++) {
				ps.setObject(i+1, vals.get(i));
			}
			result = ps.executeQuery();
			while(result.next()) {
				CarSpot c = new CarSpot();
				c.setId(result.getString("id"));
				c.setStatus(result.getString("status"));
				c.setNumber(result.getString("number"));
				list.add(c);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return list;
	}
	
	//更新车位信息
	public static int update(CarSpot cs) {
		String sql = "update carspot set status = ?,number = ? where id = ?";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			if(cs.getStatus().equals("on")) {
				ps.setString(1, "off");
			}else {
				ps.setString(1, "on");
			}
			ps.setString(2, cs.getNumber());
			ps.setString(3, cs.getId());
			int res = ps.executeUpdate();
			return res;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return 0;
	}
	

}
